// This File is part of the "nyaqt" Project
// 
// Copyright  2009 sometmes@gmail.com
// All rights reserved
// 
// This library is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published
// by the Free Software Foundation; either version 2.1 of the License, or
// (at your option) any later version.
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
// 
// You should have received a copy of the GNU General Public License
// along with this program.  If not, see <http://www.gnu.org/licenses/>.



using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Mono.Data.SybaseClient;
using nyaqt.Plugins;
using System.Globalization;

namespace nyaqt.MonoSybasePlugin
{
    class ConnectionProperties : nyaqt.Plugins.IConnectionProperties
    {
        SybaseConnection _cn = null;

        public System.Data.IDbConnection Connection
        {
            get
            {
                return this._cn;
            }
            set
            {
                if (this._cn != null)
                    this._cn.InfoMessage -= new SybaseInfoMessageEventHandler(Connnection_InfoMessage);

                this._cn = value as SybaseConnection;

                if (value != null)
                    this._cn.InfoMessage += new SybaseInfoMessageEventHandler(Connnection_InfoMessage);
            }
        }

        void Connnection_InfoMessage(object sender, SybaseInfoMessageEventArgs e)
        {
            if (ConnectionMessage != null)
            {
                foreach (SybaseError err in e.Errors)
                {
                    ConnectionMessage.Invoke(err.Message);
                }
            }
        }

        public string Server
        {
            get
            {
                return this._cn.DataSource;
            }
        }

        public string Database
        {
            get
            {
                return this._cn.Database;
            }
        }

        string userName = null;
        public string UserName
        {
            get
            {
                if (userName == null)
                {
                    SybaseCommand cmd = new SybaseCommand("select user_name()", this._cn);
                    userName = cmd.ExecuteScalar() as string;
                }
                return userName;
            }
        }

        public string ServerBrowseKey
        {
            get
            {
                return Server + "/" + UserName;
            }
        }

        public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabases()
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            SybaseCommand cmd = new SybaseCommand("sp_databases", _cn);
            SybaseDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                DatabaseEntityInfo e = new DatabaseEntityInfo();
                e.DatabaseName = r["database_name"] as string;
                ret.Add(e);
            }
            r.Close();
            return ret;
        }

        private List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseTablesEx(string database, string type)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            SybaseCommand cmd = new SybaseCommand("use " + database + "  exec sp_tables @table_type = \"'" + type + "'\" ", _cn);
            try
            {
                SybaseDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DatabaseName = r["table_qualifier"] as string;
                    e.TableNamespace = r["table_owner"] as string;
                    e.TableName = r["table_name"] as string;
                    e.TableType = type;
                    e.TableDescription = r["remarks"] as string;
                    e.DisplayName = e.TableName;
                    if (e.TableNamespace != "dbo")
                        e.DisplayName += "(" + e.TableNamespace + ")";
                    ret.Add(e);
                }
                r.Close();
            }
            catch (SybaseException ex)
            {
                if (ex.Number == 10351)
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DisplayName = ex.Message;
                    e.TableType = "ERROR";
                    ret.Add(e);
                }
                return ret;
            }
            ret.Sort();
            return ret;
        }

        public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseTables(string database)
        {
            return GetDatabaseTablesEx(database, "TABLE");
        }

        public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseViews(string database)
        {
            return GetDatabaseTablesEx(database, "VIEW");
        }

        public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseSystemTables(string database)
        {
            return GetDatabaseTablesEx(database, "SYSTEM TABLE");
        }

        public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseStoredProcedures(string database)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            SybaseCommand cmd = new SybaseCommand("use " + database + "  exec sp_stored_procedures ", _cn);
            try
            {
                SybaseDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DatabaseName = r["procedure_qualifier"] as string;
                    e.TableNamespace = r["procedure_owner"] as string;
                    e.TableName = r["procedure_name"] as string;
                    e.TableType = "STORED PROC";
                    e.TableDescription = r["remarks"] as string;
                    e.DisplayName = e.TableName;
                    if (e.TableNamespace != "dbo")
                        e.DisplayName += "(" + e.TableNamespace + ")";
                    ret.Add(e);
                }
                r.Close();
            }
            catch (SybaseException ex)
            {
                if (ex.Number == 10351)
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DisplayName = ex.Message;
                    e.TableType = "ERROR";
                    ret.Add(e);
                }
                return ret;
            }
            ret.Sort();
            return ret;
        }

        string FormatColumnType(IDataRecord col, bool withMode)
        {
            string type = "";

            object o = col["char_octet_length"];
            int char_octet_length = 0;
            if (o != DBNull.Value)
                char_octet_length = (int)o;

            string type_name = col["type_name"] as string;
            bool NULL = "YES".Equals(col["is_nullable"] as string,StringComparison.CurrentCultureIgnoreCase);
            if (char_octet_length == 0)
                type += type_name;
            else
                type += type_name + "(" + char_octet_length + ")";

            if (NULL)
                type += " null";
            else
                type += " not null";

            if (withMode)
                type = col["mode"] as string + " " + type;

            return type;
        }

        public List<DatabaseEntityInfo> GetDatabaseTableColumns(string database, string tableNamespace, string table)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            List<string> keys = new List<string>();
            SybaseCommand cmd = new SybaseCommand("use " + database + "  exec sp_pkeys @table_owner = '"+tableNamespace+"', @table_name = '"+table+"' ", _cn);
            try
            {
                SybaseDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    keys.Add(r["column_name"] as string);
                }
                r.Close();

                cmd = new SybaseCommand("use " + database + "  exec sp_columns @table_owner = '" + tableNamespace + "', @table_name = '" + table + "' ", _cn);
                r = cmd.ExecuteReader();
                while (r.Read())
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DatabaseName = r["table_qualifier"] as string;
                    e.TableNamespace = r["table_owner"] as string;
                    e.TableName = r["table_name"] as string;
                    e.ColumnName = r["column_name"] as string;
                    e.TableType = "COLUMN";
                    e.TableDescription = r["remarks"] as string;
                    if (keys.Contains(e.ColumnName))
                        e.IsPrimaryKey = true;
                    e.DisplayName = e.ColumnName + " : " + FormatColumnType(r, false);
                    ret.Add(e);
                }
                r.Close();
            }
            catch (SybaseException ex)
            {
                if (ex.Number == 10351)
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DisplayName = ex.Message;
                    e.TableType = "ERROR";
                    ret.Add(e);
                }
                return ret;
            }
            return ret;
        }

        private void GetDatabaseTableRelations(List<DatabaseEntityInfo> list, string database, string tableNamespace, string table, bool outgoing)
        {
            SybaseCommand cmd = null;
            string table_column, tableowner_column, related_column, relatedowner_column, relateddb_column;
            if (!outgoing)
            {
                table_column = "pktable_name";
                tableowner_column = "pktable_owner";
                related_column = "fktable_name";
                relatedowner_column = "fktable_owner";
                relateddb_column = "fktable_qualifier";
            }
            else
            {
                table_column = "fktable_name";
                tableowner_column = "fktable_owner";
                related_column = "pktable_name";
                relatedowner_column = "pktable_owner";
                relateddb_column = "pktable_qualifier";
            }

            cmd = new SybaseCommand("use " + database + "  exec sp_fkeys @" + tableowner_column + " = '" + tableNamespace + "', @" + table_column + " = '" + table + "' ", _cn);
        
            try
            {
                SybaseDataReader r = cmd.ExecuteReader();
                string fktable_name = "";
                while (r.Read())
                {
                    if (fktable_name != r[related_column] as string)
                    {
                        fktable_name = r[related_column] as string;

                        DatabaseEntityInfo e = new DatabaseEntityInfo();
                        e.DatabaseName = r[relateddb_column] as string;
                        e.TableNamespace = r["fktable_owner"] as string;
                        e.TableName = r["fktable_name"] as string;
                        e.RelatedTable = r["pktable_name"] as string;
                        e.RelatedTableNamespace = r["pktable_owner"] as string;
                        e.TableType = "RELATION";
                        e.DisplayName = fktable_name;
                        if (e.TableNamespace != "dbo")
                            e.DisplayName += "(" + e.TableNamespace + ")";
                        list.Add(e);
                    }
                }
                r.Close();
            }
            catch (SybaseException ex)
            {
                if (ex.Number == 10351)
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DisplayName = ex.Message;
                    e.TableType = "ERROR";
                    list.Add(e);
                }
            }
        }

        public List<DatabaseEntityInfo> GetDatabaseTableRelations(string database, string tableNamespace, string table)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            GetDatabaseTableRelations(ret, database, tableNamespace, table, false);
            GetDatabaseTableRelations(ret, database, tableNamespace, table, true);
            ret.Sort();
            return ret;
        }

        public List<DatabaseEntityInfo> GetDatabaseTableRelationColumns(string database, string tableNamespace, string table, string relatedTableNamespace, string relatedTable)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            SybaseCommand cmd = new SybaseCommand("use " + database + "  exec sp_fkeys @pktable_owner = '" + relatedTableNamespace + "', @pktable_name = '" + relatedTable + "', @fktable_owner = '" + tableNamespace + "', @fktable_name = '" + table + "' ", _cn);
            try
            {
                SybaseDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DatabaseName = r["fktable_qualifier"] as string;
                    e.TableNamespace = r["fktable_owner"] as string;
                    e.TableName = r["fktable_name"] as string;
                    e.RelatedTable = r["pktable_name"] as string;
                    e.RelatedTableNamespace = r["pktable_owner"] as string;
                    e.ColumnName = r["fkcolumn_name"] as string;
                    e.RelatedColumnName = r["pkcolumn_name"] as string;
                    e.TableType = "RELATION COLUMN";
                    e.DisplayName = e.RelatedColumnName + " = " + e.ColumnName;
                    ret.Add(e);
                }
                r.Close();
            }
            catch (SybaseException ex)
            {
                if (ex.Number == 10351)
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DisplayName = ex.Message;
                    e.TableType = "ERROR";
                    ret.Add(e);
                }
                return ret;
            }
            return ret;
        }

        public List<DatabaseEntityInfo> GetDatabaseStoredProcedureParameters(string database, string tableNamespace, string table)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            SybaseCommand cmd = new SybaseCommand("use " + database + "  exec sp_sproc_columns @procedure_owner = '" + tableNamespace + "', @procedure_name = '" + table + "' ", _cn);
            try
            {
                SybaseDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DatabaseName = r["procedure_qualifier"] as string;
                    e.TableNamespace = r["procedure_owner"] as string;
                    e.TableName = r["procedure_name"] as string;
                    e.ColumnName = r["column_name"] as string;
                    e.TableType = "COLUMN";
                    e.TableDescription = r["remarks"] as string;
                    e.DisplayName = e.ColumnName + " : " + FormatColumnType(r, true);
                    ret.Add(e);
                }
                r.Close();
            }
            catch (SybaseException ex)
            {
                if (ex.Number == 10351)
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DisplayName = ex.Message;
                    e.TableType = "ERROR";
                    ret.Add(e);
                }
                return ret;
            }
            return ret;
        }

        public string FormatSelectTopQuery(int top, DatabaseEntityInfo table, List<DatabaseEntityInfo> columns)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT TOP ").AppendLine(top.ToString());
            bool first = true;
            foreach (DatabaseEntityInfo col in columns)
            {
                sql.Append("\t");
                if (!first) sql.Append(",");
                sql.Append("[").Append(col.ColumnName).AppendLine("]");
                first = false;
            }
            sql.Append("FROM\t").Append("[").Append(table.TableNamespace).Append("].[").Append(table.TableName).AppendLine("]");
            sql.AppendLine("--WHERE");
            first = true;
            foreach (DatabaseEntityInfo col in columns)
            {
                sql.Append("--");
                sql.Append("\t");
                if (!first) sql.Append(",");
                sql.Append("[").Append(col.ColumnName).AppendLine("]");
                first = false;
            }
            return sql.ToString();
        }

        public string ConnectionStringPasswordKey
        {
            get { return "Password"; }
        }

        public event ConnectionMessageDelegate ConnectionMessage;

        public string FormatOwnException(Exception ex)
        {
            string msg = "";
            if (ex is SybaseException)
            {
                SybaseException ex2 = ex as SybaseException;
                foreach (SybaseError err in ex2.Errors)
                {
                    if (msg != "")
                        msg += "\r\n";
                    msg += err.Message;
                }
            }
            return msg;
        }

        public string LineCommentString
        {
            get { return "--"; }
        }

        public string SimulateParamsUse(IDbDataParameter[] cmdParams)
        {
            string s = "";
            foreach (IDataParameter param in cmdParams)
            {
                if (s != "")
                    s += ", ";
                s += param.ParameterName + "=" + param.ParameterName;
            }
            if (s != "")
                s = "set " + s;
            string d = "";
            foreach (SybaseParameter param in cmdParams)
            {
                if (d != "")
                    d += ", ";
                d += param.ParameterName + " " + param.SybaseType.ToString();
            }
            if (d != "")
                d = "declare " + d;
            string v = "";
            foreach (IDataParameter param in cmdParams)
            {
                if (v != "")
                    v += ", ";
                v += param.ParameterName + "=" + FormatAse(param.Value);
            }
            if (v != "")
                v = "set " + v;
            return s + "\r\n" + d + "\r\n" + v;
        }

        string FormatAse(object val)
        {
            if (val is DateTime)
            {
                DateTime dval = (DateTime)val;
                if (dval == dval.Date)
                    return string.Format("'yyyy-MM-dd'", val);
                else
                    return string.Format("'yyyy-MM-dd hh\\:mm\\:ss\\:fff'", val);
            }
            else if (val is string)
                return "'" + val + "'";
            else
                return string.Format(CultureInfo.InvariantCulture, "{0}", val);
        }
    }
}
































